<?php
/** New customre registration for model
*
* This is registration class model to access DB and insert the new customer information
*
* @author  Yogette
* @version 39
* @copyright copyright (c) 2011, Boston Univeristy Under BSD
* 
*/

class Model_register extends CI_Model {
    
 /** Add a new a User in the DB
*
*  This function will insert the new user in the DB and send a confirmation email to the new customer
*  
*  @param varchar $username This the username to be inserted
*  @param varchar $first_name This the first name to be inserted
*  @param varchar $last_name This the first name to be inserted
*  @param varchar $email This the email to be inserted
*  @param varchar $password This is the hashed password to be validated
*  @param varchar $address This the address to be inserted
*  @param varchar $zipcode This the zip code to be inserted
*  @todo must add the credit card information in the DB
* 
*   DONE
*  
*/
    
    function model_add_user($username,  $password, $first_name, $last_name, $dob, $email, $shipaddress, $billaddress, $countryname, $credit_card_type, $credit_card_number, $credit_card_month, $credit_card_year, $credit_card_cvv, $paypal)//, $movies_membership, $games_membership)
    {
       // The DB query to search for a specific username in the DB 
         $query_str = "SELECT emp_id FROM `T_EMPLOYEES` WHERE EMP_EMAIL = ?";
         // run the query with $username info to get the results of users with the same username
         $result = $this->db->query($query_str, $email);

         // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
         if ($result->num_rows() == 1)
            {
                $emp = $result->row(0)->emp_id;

            }
            else
            {
                $emp = null;               
            }
            
            
            //$query_str = "CALL ADD_CUSTOMER( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?, ?, ?)";
               
                 //$result =  $this->db->query($query_str, array($username, $password, $first_name, $last_name, $dob, $email, $shipaddress, $billaddress, $countryname, $credit_card_type, $credit_card_number, $credit_card_month, $credit_card_year, $credit_card_cvv,$paypal, $emp));     
               
                $query_str = "SELECT c_id FROM `T_COUNTRIES` WHERE NAME = ? ";
                
                $result =  $this->db->query($query_str, $countryname); 

                $cunt_id = $result->row(0)->c_id;
                
                $query_str = "INSERT INTO `T_CUS_PAYMENT` (CC_TYPE, CC_NUM, CC_DATE_MONTH, CC_DATE_YEAR, CC_3DIGIT, PAYPAL_ACC)
			VALUES(?, ?, ?, ?, ?, ?)";
                
                $result1 =  $this->db->query($query_str, array($credit_card_type, 
                            $credit_card_number, $credit_card_month, $credit_card_year, $credit_card_cvv,
                            $paypal)); 

                $query_str = "SELECT payment_id FROM `T_CUS_PAYMENT` where cc_num = ?";
                
                $result2 =  $this->db->query($query_str, $credit_card_number); 

                $pay_id = $result2->row(0)->payment_id;
                
		 $query_str = "INSERT INTO `T_CUSTOMERS`(CUS_FNAME,CUS_LNAME,CUS_EMAIL,CUS_BILLING_ADDRESS,
                     CUS_SHIPPING_ADDRESS, C_ID,CUS_PASSWORD,CUS_PAY_ID,EMP_ID,CUS_DOB,CUS_USERNAME)
		 VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                $result3 =  $this->db->query($query_str, array($first_name, 
                    $last_name, $email, $billaddress, $shipaddress, $cunt_id,$password, 
                     $pay_id, $emp , $dob, $username));     
          
                
             if ($result1 && $result3)
                {
                // sending an confirmation email for the registration
                    $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                    $this->email->to($email); 
                    $this->email->cc('zs.master@hotmail.com'); 
                    $this->email->subject('Confirmation from YOGETTE');
                    $this->email->message('This is a Confirmation for your registeration with Yogette. Congratulations, and Thank you for Joining Us');	
                    $this->email->send();
                    return true;
                }
                else
                {
                    return FALSE;
                }
    }
    
    
    
 /** check username existance in the DB
*
*  This function will check the existance of the username in the DB while registeration
*  
*  @param varchar $username This the username to be checked
*  @return binary it will return TRUE if there exist a user witht the same username and will return FALSE if there is not
*
*  
*/
    
    
    function check_exist_username($username)
    {
     
     // The DB query to search for a specific username in the DB 
     $query_str = "SELECT cus_username FROM `T_CUSTOMERS` where cus_username = ?";
     
     // run the query with $username info to get the results of users with the same username
     $result = $this->db->query($query_str, $username);
     
     // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
     if ($result->num_rows() > 0)
        {
            return true;
        }
        else
        {
            // The DB query to search for a specific username in the DB 
             $query_str2 = "SELECT emp_username FROM `T_EMPLOYEES` where emp_username = ?";

             // run the query with $username info to get the results of users with the same username
             $result2 = $this->db->query($query_str2, $username);
             if ($result2->num_rows() > 0)
                {
                    return true;
                }
                else
                {
                    return FALSE;
                }
        }
    }
    
    
 /** check email existance in the DB
*
*  This function will check the existance of the email in the DB while registeration 
*  
*  @param varchar $password This the username to be checked
*  @return binary it will return TRUE if there exist a user witht the same email and will return FALSE if there is not  
*
*  
*/
    
    function check_exist_email($email)
    {
     
     // The DB query to search for a specific email in the DB
     $query_str = "SELECT cus_email FROM `T_CUSTOMERS` where cus_email = ?";
     
     // run the query with $email info to get the results of users with the same email
     $result = $this->db->query($query_str, $email);
     
     // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
     if ($result->num_rows() > 0)
        {
            return true;
        }
        else
        {
            
            // The DB query to search for a specific email in the DB
             $query_str2 = "SELECT emp_email FROM `T_EMPLOYEES` where emp_email = ?";

             // run the query with $email info to get the results of users with the same email
             $result2 = $this->db->query($query_str2, $email);
             if ($result2->num_rows() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
        }
    }
    
    
    
    function check_author_exist_email($email)
    {
     
     // The DB query to search for a specific email in the DB
     $query_str = "SELECT auth_id  , author_email FROM `T_AUTHORS` where author_email = ?";
     
     // run the query with $email info to get the results of users with the same email
     $result = $this->db->query($query_str, $email);
     
     // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
     if ($result->num_rows() > 0)
        {
            return true;
        }
        else
        {
            return true;
        }
    }
    
    function check_exist_paypal($paypal)
    {
     
     // The DB query to search for a specific email in the DB
     $query_str = "SELECT paypal_acc FROM `T_CUS_PAYMENT` where paypal_acc = ?";
     
     // run the query with $email info to get the results of users with the same email
     $result = $this->db->query($query_str, $paypal);
     
     // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
     if ($result->num_rows() > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
     function check_exist_credit($credit_card_number)
    {
     
     // The DB query to search for a specific credit card in the DB
     $query_str = "SELECT cc_num FROM `T_CUS_PAYMENT` where cc_num = ?";
     
     // run the query with $email info to get the results of users with the same email
     $result = $this->db->query($query_str, $credit_card_number);
     
     // check if the result contains more than one row of the DB query it will return the TRUE  or  if less than or equall will return FALSE
     if ($result->num_rows() > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    
    function model_reset_password($email, $md5_password, $password)
    {
        
        $query_str = "SELECT cus_id , cus_email 
            FROM `T_CUSTOMERS` WHERE cus_email = ?
            ORDER BY cus_id ASC";
        // run the query to get the results with posts titles
        $query = $this->db->query($query_str , $email);

        if ($query->num_rows() > 0) 
            {
            // The DB query to update with the new password the new cusotomer information in the DB 

            $query_str = "UPDATE `T_CUSTOMERS` SET cus_password = ? WHERE cus_email = ?";

            //run the query to change the password
            $result = $this->db->query($query_str, array($md5_password, $email));
            if ($result) 
                {
                    $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                    $this->email->to($email); 
                    $this->email->cc('zs.master@hotmail.com'); 
                    $this->email->subject('new requested password from YOGETTE');
                    $this->email->message('This is your new generated password. Be Sure you keep it in a safe place. PASSWORD: '.$password.'');	
                    $this->email->send();
                 }
            }
     
             $query_str = "SELECT emp_id , emp_email 
                FROM `T_EMPLOYEES` WHERE EMP_email = ?
                ORDER BY emp_id ASC";
                // run the query to get the results with posts titles
                $query2 = $this->db->query($query_str , $email);

                if ($query2->num_rows() > 0) 
                    {
                        // The DB query to update with the new password the new cusotomer information in the DB 
                        $query_str = "UPDATE `T_EMPLOYEES` SET emp_password = ? WHERE emp_email = ?";
                        //run the query to change the password
                        $result2 = $this->db->query($query_str, array($md5_password, $email));
                    if ($result2) 
                        {
                        // sending an confirmation email for the registration
                            $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                            $this->email->to($email); 
                            $this->email->cc('zs.master@hotmail.com'); 
                            $this->email->subject('new requested password from YOGETTE');
                            $this->email->message('This is your new generated password. Be Sure you keep it in a safe place. PASSWORD: '.$password.'');	
                            $this->email->send();
                         }
                  }
    }
    

    function get_all_countries()
    {
        $query_str = "SELECT name 
            FROM `t_countries`
            ORDER BY c_id ASC";
        // run the query to get the results with posts titles
        $query = $this->db->query($query_str);

        if ($query->num_rows() > 0) {
            return $query->result_array();//result();
        } else {
            return array();
        }
    }   
}

?>